/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.wx.statweb.utils;

import cn.ac.iie.di.wx.statweb.vo.WarningVo;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author Administrator
 */
public class MySQLUtil {

    static Logger logger = Logger.getLogger(MySQLUtil.class.getName());

    //将获取的表格数据写成list
    public static List<WarningVo> queryTableByStatus() {
        List<WarningVo> list = new ArrayList<WarningVo>();
//        for (int i = 0; i < 10; i++) {
//            list.add(new WarningVo(String.valueOf(i + 1), "1", "这是第" + (i + 1) + "个BUG", "创建时间", "time", "备注", "0"));
//        }
        //访问mysql
        String sql = "select * from alerm where status = 0;"; //SQL语句  
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  

        try {
            ResultSet ret = db1.pst.executeQuery(sql); //执行语句，得到结果集  
            while (ret.next()) {
                list.add(new WarningVo(ret.getString(1), ret.getString(2), ret.getString(3),  ret.getString(5), ret.getString(6), ret.getString(7)));
            }//显示数据  
            ret.close();
            db1.close();//关闭连接  
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static boolean updateById(String id) {
        boolean flag = false;
        try {

            String sql = "update alerm set status = 1 where id = " + id + ";"; //SQL语句
            DBHelper db1 = new DBHelper(sql); //创建DBHelper对象
            int result = db1.pst.executeUpdate(sql);
            logger.info(result);
            if (result == 1) {
                logger.info("success");
                flag = true;
            }

        } catch (SQLException ex) {
            java.util.logging.Logger.getLogger(MySQLUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
        return flag;
    }

    public static boolean updateRemarkById(String id, String remark) throws SQLException, UnsupportedEncodingException {
        boolean flag = false;
        String sql = "update alerm set remark = '" + remark + "' where id = " + id + ";"; //SQL语句  
        sql = new String(sql.getBytes(), "UTF-8");
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  
        int result = db1.pst.executeUpdate(sql);
        System.out.println(result);
        if (result == 1) {
            System.out.println("success");
            flag = true;
        }
        return flag;
    }

    public static boolean deleteById(String id) throws UnsupportedEncodingException, SQLException {
        boolean flag = false;
        String sql = "delete from alerm where id = " + id + ";"; //SQL语句  
        sql = new String(sql.getBytes(), "UTF-8");
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  
        int result = db1.pst.executeUpdate(sql);
        System.out.println(result);
        if (result == 1) {
            System.out.println("success");
            flag = true;
        }
        return flag;
    }
}
